rm(list = ls())
load("dataset.RData")

save(list = ls(all = TRUE), file= "dataset.RData")

install.packages("")

library(jsonlite)
library(plyr)
library(tibble)
library(writexl)
library(readxl)
library(dplyr)

################################## 
########## LOAD 2024 #############
##################################

s2024 <- fromJSON("data/s2024.json")
s2024 <- s2024$parties 

### download function
download.money <- function(type = "vydaje", election = s2024){
  money <- tibble(
    ic = character(),
    shortName = character(),
    longName = character())
  for (i in 1:nrow(election)) {
    tryCatch({
      data <- as.data.frame(election$files[i])
      if (!"subject" %in% names(data)) {
        message("Skipping row ", i, ": no 'subject' column")
        next
      }
      url_vec <- data$url[data$subject == type]
      if (length(url_vec) > 0 && nzchar(url_vec[1])) {
        result <- fromJSON(url_vec[1])
        if (is.data.frame(result) && nrow(result) > 0) {
          result$ic <- election$ic[i]
          result$shortName <- election$shortName[i]
          result$longName <- election$longName[i]
          money <- rbind.fill(money, result)
          message("✔️ Row ", i, " processed successfully.")
        } else {
          message("⚠️ Empty JSON at row ", i, ": ", url_vec[1])
        }
      } else {
        message("⚠️ No valid URL for 'penizefo' at row ", i)
      }
    }, error = function(e) {
      message("❌ Error at row ", i, ": ", e$message)
    })
  }
  return(money)
}

vydaje24 <- download.money("vydaje", s2024) # download spendings
vydaje24$id <- paste0(24,"v",seq_len(nrow(vydaje24)))
penizefo <- download.money("penizefo", s2024) # download donations (people)
penizepo <- download.money("penizepo", s2024) # download donations (firms)
penize24 <- rbind.fill(penizefo, penizepo)
rm(penizefo, penizepo)
penize24$id <- paste0(24,"p",seq_len(nrow(penize24)))
bupfo <- download.money("bupfo", s2024) # download givings (people)
buppo <- download.money("buppo", s2024) # download givings (firms)
bup24 <- rbind.fill(bupfo,buppo)
rm(bupfo, buppo)
bup24$id <- paste0(24,"b",seq_len(nrow(bup24)))


##################################
########## LOAD 2022 #############
##################################

s2022 <- fromJSON("data/s2022.json")
s2022 <- s2022$parties

vydaje22 <- download.money("vydaje", s2022) # download spendings
vydaje22$id <- paste0(22,"v",seq_len(nrow(vydaje22)))
penizefo <- download.money("penizefo", s2022) # download donations (people)
penizepo <- download.money("penizepo", s2022) # download donations (firms)
penize22 <- rbind.fill(penizefo, penizepo)
rm(penizefo, penizepo)
penize22$id <- paste0(22,"p",seq_len(nrow(penize22)))
bupfo <- download.money("bupfo", s2022) # download givings (people)
buppo <- download.money("buppo", s2022) # download givings (firms)
bup22 <- rbind.fill(bupfo,buppo)
rm(bupfo, buppo)
bup22$id <- paste0(22,"b",seq_len(nrow(bup22)))


##################################
########## LOAD 2020 #############
##################################

s2020 <- fromJSON("data/s2020.json")
s2020 <- s2020$parties

vydaje20 <- download.money("vydaje", s2020) # download spendings
vydaje20$id <- paste0(20,"v",seq_len(nrow(vydaje20)))
penizefo <- download.money("penizefo", s2020) # download donations (people)
penizepo <- download.money("penizepo", s2020) # download donations (firms)
penize20 <- rbind.fill(penizefo, penizepo)
rm(penizefo, penizepo)
penize20$id <- paste0(20,"p",seq_len(nrow(penize20)))
bupfo <- download.money("bupfo", s2020) # download givings (people)
buppo <- download.money("buppo", s2020) # download givings (firms)
bup20 <- rbind.fill(bupfo,buppo)
rm(bupfo, buppo)
bup20$id <- paste0(20,"b",seq_len(nrow(bup20)))


##################################
########## LOAD 2018 #############
##################################

s2018 <- fromJSON("data/s201810.json")
s2018 <- s2018$parties

vydaje18 <- download.money("vydaje", s2018) # download spendings
vydaje18$id <- paste0(18,"v",seq_len(nrow(vydaje18)))
penizefo <- download.money("penizefo", s2018) # download donations (people)
penizepo <- download.money("penizepo", s2018) # download donations (firms)
penize18 <- rbind.fill(penizefo, penizepo)
rm(penizefo, penizepo)
penize18$id <- paste0(18,"p",seq_len(nrow(penize18)))
bupfo <- download.money("bupfo", s2018) # download givings (people)
buppo <- download.money("buppo", s2018) # download givings (firms)
bup18 <- rbind.fill(bupfo,buppo)
rm(bupfo, buppo)
bup18$id <- paste0(18,"b",seq_len(nrow(bup18)))


###############################
########## EXPORT #############
###############################

vydaje <- list(vydaje18 = vydaje18, vydaje20 = vydaje20, vydaje22 = vydaje22, vydaje24 = vydaje24)
write_xlsx(vydaje, "data/vydaje.xlsx")
penize <- list(penize18 = penize18, penize20 = penize20, penize22 = penize22, penize24 = penize24)
write_xlsx(penize, "data/penize.xlsx")
bup <- list(bup18 = bup18, bup20 = bup20, bup22 = bup22, bup24 = bup24)
write_xlsx(bup, "data/bup.xlsx")


####################################################################################################################################
########## LOAD SENATE ###########
##################################

serk24 <- read_excel("data/serk24.xlsx")
serk22 <- read_excel("data/serk22.xlsx")
serk20 <- read_excel("data/serk20.xlsx")
serk18 <- read_excel("data/serk18.xlsx")

cvs24 <- read_excel("data/SE2024ciselniky20240928_xlsx/cvs.xlsx")
cvs22 <- read_excel("data/SE2022ciselniky20220916/cvs.xlsx")
cvs20 <- read_excel("data/SE2020ciselnik20200918/cvs.xlsx")
cvs18 <- read_excel("data/SE2018ciselnik20181004/cvs.xlsx")

serk24 <- merge(serk24,cvs24[,c("VSTRANA","ZKRATKAV30","ZKRATKAV8")])
serk22 <- merge(serk22,cvs22[,c("VSTRANA","ZKRATKAV30","ZKRATKAV8")])
serk20 <- merge(serk20,cvs20[,c("VSTRANA","ZKRATKAV30","ZKRATKAV8")])
serk18 <- merge(serk18,cvs18[,c("VSTRANA","ZKRATKAV30","ZKRATKAV8")])

table(serk24$ZKRATKAV8)
table(serk22$ZKRATKAV8)
table(serk20$ZKRATKAV8)
table(serk18$ZKRATKAV8)


##################################
########## LOAD ADJUSTED DATA ####
##################################

fvydaje24 <- read_excel("data/vydaje_adj.xlsx", sheet = "vydaje24")
fvydaje22 <- read_excel("data/vydaje_adj.xlsx", sheet = "vydaje22")
fvydaje20 <- read_excel("data/vydaje_adj.xlsx", sheet = "vydaje20")
fvydaje18 <- read_excel("data/vydaje_adj.xlsx", sheet = "vydaje18")

fpenize24 <- read_excel("data/penize_adj.xlsx", sheet = "penize24")
fpenize22 <- read_excel("data/penize_adj.xlsx", sheet = "penize22")
fpenize20 <- read_excel("data/penize_adj.xlsx", sheet = "penize20")
fpenize18 <- read_excel("data/penize_adj.xlsx", sheet = "penize18")

fbup24 <- read_excel("data/bup_adj.xlsx", sheet = "bup24")
fbup22 <- read_excel("data/bup_adj.xlsx", sheet = "bup22")
fbup20 <- read_excel("data/bup_adj.xlsx", sheet = "bup20")
fbup18 <- read_excel("data/bup_adj.xlsx", sheet = "bup18")


fvydaje22$constituency4 <- as.numeric(fvydaje22$constituency4)

##################################
########## FINALIZE DATASETS #####
##################################

fvydaje24$election <- 2024
fvydaje22$election <- 2022
fvydaje20$election <- 2020
fvydaje18$election <- 2018

fvydaje22 <- fvydaje22[!(fvydaje22$id %in% c("22v2507", "22v2508")),] # ODS odečty, které se pak objevují ale v jejich koalicích
fvydaje24 <- fvydaje24[!(fvydaje24$id %in% c("24v1775", "24v1776","24v1777")),]  # KSČM odečty, které se pak objevují ale v jejich koalicích
fvydaje24 <- fvydaje24[!(fvydaje24$id %in% c("24v2479", "24v2480", "24v2481", "24v2482", "24v2483", "24v2484", "24v2485")),] # ODS odečty, které se pak objevují ale v jejich koalicích

cvs24$duplicate <- duplicated(cvs24$ZKRATKAV8)
cvs24$ZKRATKAV8[cvs24$duplicate==T & cvs24$ZKRATKAV8 %in% fvydaje24$ZKRATKAV8]
cvs24 <- cvs24[cvs24$VSTRANA!=120,]
fvydaje24 <- merge(fvydaje24,cvs24[,c("ZKRATKAV8","VSTRANA","SLOZENI")],by="ZKRATKAV8")
cvs22$duplicate <- duplicated(cvs22$ZKRATKAV8)
cvs22$ZKRATKAV8[cvs22$duplicate==T & cvs22$ZKRATKAV8 %in% fvydaje22$ZKRATKAV8]
cvs22 <- cvs22[cvs22$VSTRANA!=120,]
fvydaje22$ZKRATKAV8 <- gsub("\u00A0", " ", fvydaje22$ZKRATKAV8, fixed = TRUE)
fvydaje22 <- merge(fvydaje22,cvs22[,c("ZKRATKAV8","VSTRANA","SLOZENI")],by="ZKRATKAV8")
cvs20$duplicate <- duplicated(cvs20$ZKRATKAV8)
cvs20$ZKRATKAV8[cvs20$duplicate==T & cvs20$ZKRATKAV8 %in% fvydaje20$ZKRATKAV8]
cvs20 <- cvs20[cvs20$VSTRANA!=120,]
fvydaje20$ZKRATKAV8 <- gsub("\u00A0", " ", fvydaje20$ZKRATKAV8, fixed = TRUE)
fvydaje20 <- merge(fvydaje20,cvs20[,c("ZKRATKAV8","VSTRANA","SLOZENI")],by="ZKRATKAV8")
cvs18$duplicate <- duplicated(cvs18$ZKRATKAV8)
cvs18$ZKRATKAV8[cvs18$duplicate==T & cvs18$ZKRATKAV8 %in% fvydaje18$ZKRATKAV8]
cvs18 <- cvs18[cvs18$VSTRANA!=120,]
fvydaje18$ZKRATKAV8 <- gsub("\u00A0", " ", fvydaje18$ZKRATKAV8, fixed = TRUE)
fvydaje18 <- merge(fvydaje18,cvs18[,c("ZKRATKAV8","VSTRANA","SLOZENI")],by="ZKRATKAV8")



fvydaje24$constituency <- NA
for (i in 1:nrow(fvydaje24)) {
  fvydaje24$constituency[i] <- paste(serk24$OBVOD[serk24$VSTRANA==fvydaje24$VSTRANA[i]],collapse = ",")
}
cols <- paste0("constituency", 1:6)
fvydaje24$constituency1 <- apply(fvydaje24[cols], 1, function(row) {
  hodnoty <- row[!is.na(row)]
  paste(hodnoty, collapse = ",")
})
fvydaje24$constituency <- ifelse(fvydaje24$constituency1=="" | fvydaje24$constituency1==99,fvydaje24$constituency,fvydaje24$constituency1)
fvydaje24 <- fvydaje24[ , !(names(fvydaje24) %in% paste0("constituency", 1:6))]
fvydaje22$constituency <- NA
for (i in 1:nrow(fvydaje22)) {
  fvydaje22$constituency[i] <- paste(serk22$OBVOD[serk22$VSTRANA==fvydaje22$VSTRANA[i]],collapse = ",")
}
cols <- paste0("constituency", 1:6)
fvydaje22$constituency1 <- apply(fvydaje22[cols], 1, function(row) {
  hodnoty <- row[!is.na(row)]
  paste(hodnoty, collapse = ",")
})
fvydaje22$constituency <- ifelse(fvydaje22$constituency1=="" | fvydaje22$constituency1==99,fvydaje22$constituency,fvydaje22$constituency1)
fvydaje22 <- fvydaje22[ , !(names(fvydaje22) %in% paste0("constituency", 1:6))]
fvydaje20$constituency <- NA
for (i in 1:nrow(fvydaje20)) {
  fvydaje20$constituency[i] <- paste(serk20$OBVOD[serk20$VSTRANA==fvydaje20$VSTRANA[i]],collapse = ",")
}
cols <- paste0("constituency", 1:5)
fvydaje20$constituency1 <- apply(fvydaje20[cols], 1, function(row) {
  hodnoty <- row[!is.na(row)]
  paste(hodnoty, collapse = ",")
})
fvydaje20$constituency <- ifelse(fvydaje20$constituency1=="" | fvydaje20$constituency1==99,fvydaje20$constituency,fvydaje20$constituency1)
fvydaje20 <- fvydaje20[ , !(names(fvydaje20) %in% paste0("constituency", 1:5))]
fvydaje18$constituency <- NA
for (i in 1:nrow(fvydaje18)) {
  fvydaje18$constituency[i] <- paste(serk18$OBVOD[serk18$VSTRANA==fvydaje18$VSTRANA[i]],collapse = ",")
}
cols <- paste0("constituency", 1:5)
fvydaje18$constituency1 <- apply(fvydaje18[cols], 1, function(row) {
  hodnoty <- row[!is.na(row)]
  paste(hodnoty, collapse = ",")
})
fvydaje18$constituency <- ifelse(fvydaje18$constituency1=="" | fvydaje18$constituency1==99,fvydaje18$constituency,fvydaje18$constituency1)
fvydaje18 <- fvydaje18[ , !(names(fvydaje18) %in% paste0("constituency", 1:5))]
fvydaje18$partiesName <- NA
for (i in 1:nrow(fvydaje18)) {
  fvydaje18$partiesName[i] <- paste(cvs18$ZKRATKAV8[cvs18$VSTRANA %in% as.numeric(strsplit(fvydaje18$SLOZENI[i], ",")[[1]])],collapse = ",")
}
fvydaje20$partiesName <- NA
for (i in 1:nrow(fvydaje20)) {
  fvydaje20$partiesName[i] <- paste(cvs20$ZKRATKAV8[cvs20$VSTRANA %in% as.numeric(strsplit(fvydaje20$SLOZENI[i], ",")[[1]])],collapse = ",")
}
fvydaje22$partiesName <- NA
for (i in 1:nrow(fvydaje22)) {
  fvydaje22$partiesName[i] <- paste(cvs22$ZKRATKAV8[cvs22$VSTRANA %in% as.numeric(strsplit(fvydaje22$SLOZENI[i], ",")[[1]])],collapse = ",")
}
fvydaje24$partiesName <- NA
for (i in 1:nrow(fvydaje24)) {
  fvydaje24$partiesName[i] <- paste(cvs24$ZKRATKAV8[cvs24$VSTRANA %in% as.numeric(strsplit(fvydaje24$SLOZENI[i], ",")[[1]])],collapse = ",")
}
finalvydaje <- rbind(fvydaje18, fvydaje20, fvydaje22, fvydaje24)
finalvydaje$expenseDate <- as.Date(finalvydaje$expenseDate)
finalvydaje$movementDate <- as.Date(finalvydaje$movementDate)
finalvydaje$birthDate <- as.Date(finalvydaje$birthDate)
names(finalvydaje)[names(finalvydaje) == "ZKRATKAV8"] <- "shortName"
names(finalvydaje)[names(finalvydaje) == "VSTRANA"] <- "candidateList"
names(finalvydaje)[names(finalvydaje) == "SLOZENI"] <- "parties"

fpenize24$election <- 2024
fpenize22$election <- 2022
fpenize20$election <- 2020
fpenize18$election <- 2018

fpenize18$shortName <- gsub("\u00A0", " ", fpenize18$shortName, fixed = TRUE)
fpenize18 <- merge(fpenize18,cvs18[,c("ZKRATKAV8","VSTRANA","SLOZENI")],by.x = "shortName",by.y="ZKRATKAV8")
fpenize20$shortName <- gsub("\u00A0", " ", fpenize20$shortName, fixed = TRUE)
fpenize20 <- merge(fpenize20,cvs20[,c("ZKRATKAV8","VSTRANA","SLOZENI")],by.x = "shortName",by.y="ZKRATKAV8")
fpenize22$shortName <- gsub("\u00A0", " ", fpenize22$shortName, fixed = TRUE)
fpenize22 <- merge(fpenize22,cvs22[,c("ZKRATKAV8","VSTRANA","SLOZENI")],by.x = "shortName",by.y="ZKRATKAV8")
fpenize24$shortName <- gsub("\u00A0", " ", fpenize24$shortName, fixed = TRUE)
fpenize24 <- merge(fpenize24,cvs24[,c("ZKRATKAV8","VSTRANA","SLOZENI")],by.x = "shortName",by.y="ZKRATKAV8")
fpenize18$test <- NA
for (i in 1:nrow(fpenize18)) {
  fpenize18$test[i] <- paste(serk18$OBVOD[serk18$VSTRANA==fpenize18$VSTRANA[i]],collapse = ",")
}
fpenize18$constituency <- ifelse(is.na(fpenize18$constituency),fpenize18$test,fpenize18$constituency)
fpenize20$test <- NA
for (i in 1:nrow(fpenize20)) {
  fpenize20$test[i] <- paste(serk20$OBVOD[serk20$VSTRANA==fpenize20$VSTRANA[i]],collapse = ",")
}
fpenize20$constituency <- ifelse(is.na(fpenize20$constituency),fpenize20$test,fpenize20$constituency)
fpenize22$test <- NA
for (i in 1:nrow(fpenize22)) {
  fpenize22$test[i] <- paste(serk22$OBVOD[serk22$VSTRANA==fpenize22$VSTRANA[i]],collapse = ",")
}
fpenize22$constituency <- ifelse(is.na(fpenize22$constituency),fpenize22$test,fpenize22$constituency)
fpenize24$test <- NA
for (i in 1:nrow(fpenize24)) {
  fpenize24$test[i] <- paste(serk24$OBVOD[serk24$VSTRANA==fpenize24$VSTRANA[i]],collapse = ",")
}
fpenize24$constituency <- ifelse(is.na(fpenize24$constituency),fpenize24$test,fpenize24$constituency)
fpenize18$partiesName <- NA
for (i in 1:nrow(fpenize18)) {
  fpenize18$partiesName[i] <- paste(cvs18$ZKRATKAV8[cvs18$VSTRANA %in% as.numeric(strsplit(fpenize18$SLOZENI[i], ",")[[1]])],collapse = ",")
}
fpenize20$partiesName <- NA
for (i in 1:nrow(fpenize20)) {
  fpenize20$partiesName[i] <- paste(cvs20$ZKRATKAV8[cvs20$VSTRANA %in% as.numeric(strsplit(fpenize20$SLOZENI[i], ",")[[1]])],collapse = ",")
}
fpenize22$partiesName <- NA
for (i in 1:nrow(fpenize22)) {
  fpenize22$partiesName[i] <- paste(cvs22$ZKRATKAV8[cvs22$VSTRANA %in% as.numeric(strsplit(fpenize22$SLOZENI[i], ",")[[1]])],collapse = ",")
}
fpenize24$partiesName <- NA
for (i in 1:nrow(fpenize24)) {
  fpenize24$partiesName[i] <- paste(cvs24$ZKRATKAV8[cvs24$VSTRANA %in% as.numeric(strsplit(fpenize24$SLOZENI[i], ",")[[1]])],collapse = ",")
}
finalpenize <- rbind(fpenize18, fpenize20, fpenize22, fpenize24)
finalpenize <- finalpenize[, !(names(finalpenize) %in% "test")]
finalpenize$birthDate <- as.Date(finalpenize$birthDate)
finalpenize$date <- as.Date(finalpenize$date)
names(finalpenize)[names(finalpenize) == "VSTRANA"] <- "candidateList"
names(finalpenize)[names(finalpenize) == "SLOZENI"] <- "parties"

fbup24$election <- 2024
fbup22$election <- 2022
fbup20$election <- 2020
fbup18$election <- 2018

fbup18$shortName <- gsub("\u00A0", " ", fbup18$shortName, fixed = TRUE)
fbup18 <- merge(fbup18,cvs18[,c("ZKRATKAV8","VSTRANA","SLOZENI")],by.x = "shortName",by.y="ZKRATKAV8")
fbup20$shortName <- gsub("\u00A0", " ", fbup20$shortName, fixed = TRUE)
fbup20 <- merge(fbup20,cvs20[,c("ZKRATKAV8","VSTRANA","SLOZENI")],by.x = "shortName",by.y="ZKRATKAV8")
fbup22$shortName <- gsub("\u00A0", " ", fbup22$shortName, fixed = TRUE)
fbup22 <- merge(fbup22,cvs22[,c("ZKRATKAV8","VSTRANA","SLOZENI")],by.x = "shortName",by.y="ZKRATKAV8")
fbup24$shortName <- gsub("\u00A0", " ", fbup24$shortName, fixed = TRUE)
fbup24 <- merge(fbup24,cvs24[,c("ZKRATKAV8","VSTRANA","SLOZENI")],by.x = "shortName",by.y="ZKRATKAV8")
for (i in 1:nrow(fbup24)) {
  fbup24$constituency[i] <- ifelse(is.na(fbup24$constituency[i]),paste(serk24$OBVOD[serk24$VSTRANA==fbup24$VSTRANA[i]],collapse = ","),
                                fbup24$constituency[i])
}
for (i in 1:nrow(fbup22)) {
  fbup22$constituency[i] <- ifelse(is.na(fbup22$constituency[i]),paste(serk22$OBVOD[serk22$VSTRANA==fbup22$VSTRANA[i]],collapse = ","),
                                   fbup22$constituency[i])
}
for (i in 1:nrow(fbup20)) {
  fbup20$constituency[i] <- ifelse(is.na(fbup20$constituency[i]),paste(serk20$OBVOD[serk20$VSTRANA==fbup20$VSTRANA[i]],collapse = ","),
                                   fbup20$constituency[i])
}
for (i in 1:nrow(fbup18)) {
  fbup18$constituency[i] <- ifelse(is.na(fbup18$constituency[i]),paste(serk18$OBVOD[serk18$VSTRANA==fbup18$VSTRANA[i]],collapse = ","),
                                   fbup18$constituency[i])
}
fbup18$partiesName <- NA
for (i in 1:nrow(fbup18)) {
  fbup18$partiesName[i] <- paste(cvs18$ZKRATKAV8[cvs18$VSTRANA %in% as.numeric(strsplit(fbup18$SLOZENI[i], ",")[[1]])],collapse = ",")
}
fbup20$partiesName <- NA
for (i in 1:nrow(fbup20)) {
  fbup20$partiesName[i] <- paste(cvs20$ZKRATKAV8[cvs20$VSTRANA %in% as.numeric(strsplit(fbup20$SLOZENI[i], ",")[[1]])],collapse = ",")
}
fbup22$partiesName <- NA
for (i in 1:nrow(fbup22)) {
  fbup22$partiesName[i] <- paste(cvs22$ZKRATKAV8[cvs22$VSTRANA %in% as.numeric(strsplit(fbup22$SLOZENI[i], ",")[[1]])],collapse = ",")
}
fbup24$partiesName <- NA
for (i in 1:nrow(fbup24)) {
  fbup24$partiesName[i] <- paste(cvs24$ZKRATKAV8[cvs24$VSTRANA %in% as.numeric(strsplit(fbup24$SLOZENI[i], ",")[[1]])],collapse = ",")
}
finalbup <- rbind(fbup18, fbup20, fbup22, fbup24)
finalbup$birthDate <- as.Date(finalbup$birthDate)
finalbup$date <- as.Date(finalbup$date)
names(finalbup)[names(finalbup) == "VSTRANA"] <- "candidateList"
names(finalbup)[names(finalbup) == "SLOZENI"] <- "parties"

# harmonize ic
finalvydaje$ic <- sub("^0+", "", finalvydaje$ic)
finalvydaje$ic <- as.numeric(finalvydaje$ic)
longname_ic_map <- finalvydaje %>%
  group_by(longName) %>%
  summarize(ic_assigned = first(na.omit(ic)), .groups = "drop") %>%
  mutate(ic_assigned = if_else(is.na(ic_assigned), 100000000 + row_number(), ic_assigned))
finalvydaje <- finalvydaje %>%
  left_join(longname_ic_map, by = "longName") %>%
  mutate(ic = ic_assigned) %>%
  select(-ic_assigned)
finalpenize$ic <- sub("^0+", "", finalpenize$ic)
finalpenize$ic <- as.numeric(finalpenize$ic)
longname_ic_map <- finalvydaje %>%
  select(longName, ic) %>%
  distinct()
finalpenize <- finalpenize %>%
  left_join(longname_ic_map, by = "longName", suffix = c("", "_from_vydaje")) %>%
  mutate(ic = if_else(is.na(ic), ic_from_vydaje, ic)) %>%
  select(-ic_from_vydaje)
finalbup$ic <- sub("^0+", "", finalbup$ic)
finalbup$ic <- as.numeric(finalbup$ic)
finalbup <- finalbup %>%
  left_join(longname_ic_map, by = "longName", suffix = c("", "_from_vydaje")) %>%
  mutate(ic = if_else(is.na(ic), ic_from_vydaje, ic)) %>%
  select(-ic_from_vydaje)
finalbup$value <- as.numeric(finalbup$value)

# sort variables
names(finalvydaje)
finalvydaje <- finalvydaje[, c("ic","shortName","longName","candidateList","parties","partiesName","election","constituency",
                               "id","description","usualPrice","money","noMoney","code","candidate","expenseDate","movementDate",
                               "documentNo","donorType","lastName","firstName","titleBefore","titleAfter","birthDate","addrCity","addrStreet","addrZip",
                               "companyId","company")]
names(finalpenize)
finalpenize <- finalpenize[, c("ic","shortName","longName","candidateList","parties","partiesName","election","constituency",
                               "id","money","date","lastName","firstName","titleBefore","titleAfter","birthDate","addrCity","addrStreet","addrZip",
                               "companyId","company")]
names(finalbup)
finalbup <- finalbup[, c("ic","shortName","longName","candidateList","parties","partiesName","election","constituency",
                               "id","description","value","date","lastName","firstName","titleBefore","titleAfter","birthDate","addrCity","addrStreet","addrZip",
                               "companyId","company")]
# type of variables
finalvydaje$titleAfter <- as.character(finalvydaje$titleAfter)

# export
write_xlsx(list(expenditures = finalvydaje, donations = finalpenize, contributions = finalbup),"Czech_senate_campaign_finance_dataset.xlsx")
expenditures <- finalvydaje
donations <- finalpenize
contributions <- finalbup
save(expenditures, donations, contributions, file = "Czech_senate_campaign_finance_dataset.RData")

colnames(expenditures)
colnames(donations)
colnames(contributions)


